<?php
namespace Plu\Library\Models\Tga\Live;

use Phalcon\Mvc\Model,
    Phalcon\Db\Column,
    Phalcon\Mvc\Model\Resultset\Simple as Resultset,
    Plu\Library\Models\Platform\Tag\TagMap as TagMapModel;

class CollegeRooms extends Rooms {
    const query_college_rooms = "SELECT tgarooms.*
        FROM tgarooms
        LEFT JOIN CollegeRooms AS cm ON (tgarooms.id = cm.RoomId)
        WHERE %s
        ORDER BY %s
        LIMIT %d, %d";

    const query_count_college_rooms = "SELECT count(1) as sumatory
        FROM tgarooms
        LEFT JOIN CollegeRooms AS cm ON (tgarooms.id = cm.RoomId)
        WHERE %s";

    /**
     * 取交集数据
     */
    const query_rooms_match_tag_intersect = "SELECT tgarooms.*
        FROM tgarooms
        LEFT JOIN CollegeRooms AS cm ON (tgarooms.id = cm.RoomId)
        LEFT JOIN TagMap AS tm ON (tgarooms.id = tm.ItemId)
        WHERE %s
        GROUP BY tgarooms.id HAVING COUNT(1) %s %d
        ORDER BY %s
        LIMIT %d, %d";

    const query_count_match_tag_intersect = "SELECT COUNT(1) as sumatory FROM (
        SELECT 1 FROM tgarooms
        LEFT JOIN CollegeRooms AS cm ON (tgarooms.id = cm.RoomId)
        LEFT JOIN TagMap AS tm ON (tgarooms.id = tm.ItemId)
        WHERE %s
        GROUP BY tgarooms.id HAVING COUNT(1) %s %d) AS T";

    const query_count_match_tag_minus = "SELECT COUNT(1) as sumatory FROM tgarooms
        LEFT JOIN CollegeRooms AS cm ON (tgarooms.id = cm.RoomId)
        WHERE %s AND EXISTS        
        (
            SELECT * FROM TagMap as tm WHERE %s %s not EXISTS (SELECT * from TagMap where ItemType = 3 AND %s AND ItemId = tm.ItemId) AND tgarooms.id = tm.ItemId
        )";

    const query_rooms_match_tag_minus = "SELECT tgarooms.* FROM tgarooms
        LEFT JOIN CollegeRooms AS cm ON (tgarooms.id = cm.RoomId)
        WHERE %s AND EXISTS        
        (
            SELECT * FROM TagMap as tm WHERE ItemType = 3 AND %s %s not EXISTS (SELECT * from TagMap where ItemType = 3 AND %s AND ItemId = tm.ItemId) AND tgarooms.id = tm.ItemId
        )
        ORDER BY %s
        LIMIT %d, %d";

    /**
     * 根据college来使用
     */
    public function setColleges($colleges) {
        $w = array();
        for ($i = 0, $len = count($colleges); $i < $len; $i++) {
            $collegeid = intval($colleges[$i]);
            $p = "cl{$i}";
            if ($collegeid > 0) {
                $w[] = "CollegeId = :{$p}:";
                $this->attr_bind[$p] = $collegeid;
                $this->attr_bindTypes[$p] = Column::BIND_PARAM_INT;
            }
        }
        if (!empty($w)) {
            $this->attr_where[] = "(". join(" OR ", $w) . ")";
        }

        return $this;
    }

    public function setExcolleges($colleges) {
        $w = array();
        for ($i = 0, $len = count($colleges); $i < $len; $i++) {
            $collegeid = intval($colleges[$i]);
            $p = "ncl{$i}";
            if ($collegeid > 0) {
                $w[] = "CollegeId != :{$p}:";
                $this->attr_bind[$p] = $collegeid;
                $this->attr_bindTypes[$p] = Column::BIND_PARAM_INT;
            }
        }
        if (!empty($w)) {
            $this->attr_where[] = "(". join(" AND ", $w) . ")";
        }

        return $this;
    }

    public function setParentColleges($colleges) {
        $w = array();
        for ($i = 0, $len = count($colleges); $i < $len; $i++) {
            $collegeid = intval($colleges[$i]);
            $p = "pcl{$i}";
            if ($collegeid > 0) {
                $w[] = "CollegeParentId = :{$p}:";
                $this->attr_bind[$p] = $collegeid;
                $this->attr_bindTypes[$p] = Column::BIND_PARAM_INT;
            }
        }
        if (!empty($w)) {
            $this->attr_where[] = "(". join(" OR ", $w) . ")";
        }

        return $this;
    }

    public function setExparentColleges($colleges) {
        $w = array();
        for ($i = 0, $len = count($colleges); $i < $len; $i++) {
            $collegeid = intval($colleges[$i]);
            $p = "npcl{$i}";
            if ($collegeid > 0) {
                $w[] = "CollegeParentId != :{$p}:";
                $this->attr_bind[$p] = $collegeid;
                $this->attr_bindTypes[$p] = Column::BIND_PARAM_INT;
            }
        }
        if (!empty($w)) {
            $this->attr_where[] = "(". join(" AND ", $w) . ")";
        }

        return $this;
    }

    public function setCities($cities) {
        $w = array();
        for ($i = 0, $len = count($cities); $i < $len; $i++) {
            $city = intval($cities[$i]);
            $p = "cty{$i}";
            if ($city > 0) {
                $w[] = "CityId = :{$p}:";
                $this->attr_bind[$p] = $city;
                $this->attr_bindTypes[$p] = Column::BIND_PARAM_INT;
            }
        }
        if (!empty($w)) {
            $this->attr_where[] = "(". join(" OR ", $w) . ")";
        }

        return $this;
    }

    public function setExcities($cities) {
        $w = array();
        for ($i = 0, $len = count($cities); $i < $len; $i++) {
            $city = intval($cities[$i]);
            $p = "ncty{$i}";
            if ($city > 0) {
                $w[] = "CityId != :{$p}:";
                $this->attr_bind[$p] = $city;
                $this->attr_bindTypes[$p] = Column::BIND_PARAM_INT;
            }
        }
        if (!empty($w)) {
            $this->attr_where[] = "(". join(" AND ", $w) . ")";
        }

        return $this;
    }

    public function setProvinces($provinces) {
        $w = array();
        for ($i = 0, $len = count($provinces); $i < $len; $i++) {
            $province = intval($provinces[$i]);
            $p = "prv{$i}";
            if ($province > 0) {
                $w[] = "ProvinceId = :{$p}:";
                $this->attr_bind[$p] = $province;
                $this->attr_bindTypes[$p] = Column::BIND_PARAM_INT;
            }
        }
        if (!empty($w)) {
            $this->attr_where[] = "(". join(" OR ", $w) . ")";
        }

        return $this;
    }

    public function setExprovinces($provinces) {
        $w = array();
        for ($i = 0, $len = count($provinces); $i < $len; $i++) {
            $province = intval($provinces[$i]);
            $p = "nprv{$i}";
            if ($province > 0) {
                $w[] = "ProvinceId != :{$p}:";
                $this->attr_bind[$p] = $province;
                $this->attr_bindTypes[$p] = Column::BIND_PARAM_INT;
            }
        }
        if (!empty($w)) {
            $this->attr_where[] = "(". join(" AND ", $w) . ")";
        }

        return $this;
    }

    public function setDeleteFlag($flag = false) {
    }

    public function reset() {
        $this->attr_where      = [];
        $this->attr_bind       = array();
        $this->attr_bindTypes  = array();
        $this->attr_roomTags   = array();
        $this->attr_exroomTags = array();

        return $this;
    }

    public function getStreams($start_index, $max_results, $sortby, $roomTags = array(), $exroomTags = array(), $strict = false) {
        $sort = "";

        //热门推荐处理
        switch ($sortby) {
            case 'newbie':
                $this->attr_where[] = " ugc = 1 ";
                $sort = "views DESC";
                break;
            case 'hotrecommend': //热推
                $this->attr_where[] = " (ugc = 3 OR ugc = 4) ";
                $sort = "views DESC";
            case "officalcert": //官方认证 不含ugc数据
                $this->attr_where[] = " (ugc = 2 or ugc = 3 OR ugc = 4) ";
                $sort = "stick DESC, views DESC";
                break;
            case 'newest':
                $sort = "broadcast_begin DESC";
                break;
            case 'views':
                $sort = "views DESC";
                break;
            case 'followers':
                $sort = 'followers DESC';
                break;
            case 'weight':
                $sort = 'weight DESC';//根据权重分数排序
                break;
            default: //老版本, 默认为top
                $sort = "stick DESC, views DESC";
                break;
        }

        $roomTags = array_merge($this->attr_roomTags, $roomTags);
        $exroomTags = array_merge($this->attr_exroomTags, $exroomTags);

        $connection = $this->getReadConnection();
        if (empty($roomTags) && empty($exroomTags)) {
            $where = join(" AND ", $this->attr_where);

            //clean tgarooms
            $where = str_replace('tgarooms.', '', $where);

            //取出数量
            $phql = sprintf(self::query_count_college_rooms, $where);
            $c = $connection->convertBoundParams($phql, $this->attr_bind);
            $phql = $c['sql'];
            $_bind = $c['params'];
            $items = new Resultset(null, $this, $this->getReadConnection()->query($phql, $_bind));
            $totalCount = $items->toArray()[0]['sumatory'];

            //获得结果集
            $phql = sprintf(self::query_college_rooms, $where, $sort, $start_index, $max_results);
            $c = $connection->convertBoundParams($phql, $this->attr_bind);
            $phql = $c['sql'];
            $_bind = $c['params'];
            $items = new Resultset(null, $this, $this->getReadConnection()->query($phql, $_bind));
        } else {

            //生成where语句
            if (!empty($roomTags)) {
                $roomTags = array_values(array_unique($roomTags));

                $roomTagsSql = array();
                $len = count($roomTags);

                for ($c = 0; $c < $len; $c++) {
                    $p = "trt{$c}";
                    $roomTagsSql[] = " TagName = :{$p}: ";
                    $this->attr_bind[$p] = $roomTags[$c];
                    $this->attr_bindTypes[$p] = Column::BIND_PARAM_STR;
                }
            }

            if (!empty($exroomTags)) {
                $exroomTags = array_values(array_unique($exroomTags));

                $exroomTagsSql = array();
                $len = count($exroomTags);

                for ($c = 0; $c < $len; $c++) {
                    $p = "tnrt{$c}";
                    $exroomTagsSql[] = " TagName = :{$p}: ";
                    $this->attr_bind[$p] = $exroomTags[$c];
                    $this->attr_bindTypes[$p] = Column::BIND_PARAM_STR;
                }
            }

            if (!empty($exroomTagsSql)) {
                //使用差集
                if (!empty($roomTagsSql)) {
                    $roomTagsSql = '(' . join( ' OR ', $roomTagsSql) . ')';
                }

                $exroomTagsSql = '(' . join( ' OR ', $exroomTagsSql) . ')';

                $where = join(" AND ", $this->attr_where);

                //取出数量
                $phql = sprintf(self::query_count_match_tag_minus, $where, !empty($roomTagsSql) ? $roomTagsSql : '', !empty($roomTagsSql) ? 'AND' : '', $exroomTagsSql);

                $c = $connection->convertBoundParams($phql, $this->attr_bind);
                $phql = $c['sql'];
                $_bind = $c['params'];

                $items = new Resultset(null, $this, $this->getReadConnection()->query($phql, $_bind));
                $totalCount = $items->toArray()[0]['sumatory'];

                //获得结果集
                $phql = sprintf(self::query_rooms_match_tag_minus, $where, !empty($roomTagsSql) ? $roomTagsSql : '', !empty($roomTagsSql) ? 'AND' : '', $exroomTagsSql, $sort, $start_index, $max_results);
                $c = $connection->convertBoundParams($phql, $this->attr_bind);
                $phql = $c['sql'];
                $_bind = $c['params'];
                $items = new Resultset(null, $this, $this->getReadConnection()->query($phql, $_bind));
            } elseif (!empty($roomTagsSql)) {
                //使用交集
                $this->attr_where[] = '(' . join( ' OR ', $roomTagsSql) . ')';
                $count = count($roomTags);

                //强制使用ItemType = 3
                $this->attr_where[] = sprintf("ItemType = %d", TagMapModel::ITEM_TYPES['ROOM']);

                $where = join(" AND ", $this->attr_where);
                //总数据
                $phql = sprintf(self::query_count_match_tag_intersect, $where, $strict ? '=' : '>', $strict ? $count : 0);


                $c = $connection->convertBoundParams($phql, $this->attr_bind);
                $phql = $c['sql'];
                $_bind = $c['params'];
                $_bindTypes = array_values($this->attr_bindTypes);
                $items = new Resultset(null, $this, $this->getReadConnection()->query($phql, $_bind, $_bindTypes));

                $totalCount = $items->toArray()[0]['sumatory'];


                $phql = sprintf(self::query_rooms_match_tag_intersect, $where, $strict ? '=' : '>', $strict ? $count : 0, $sort, $start_index, $max_results);
                $c = $connection->convertBoundParams($phql, $this->attr_bind);
                $phql = $c['sql'];
                $_bind = $c['params'];
                $items = new Resultset(null, $this, $this->getReadConnection()->query($phql, $_bind, $_bindTypes));
            }
        }

        if (empty($totalCount)) {
            $totalCount = 0;
        }

        if (count($items)) {
            $items = $items->toArray();
        } else {
            $items = [];
        }


        $stars = array($totalCount, $items);

        return $stars;
    }
}

?>
